package pos.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import servicios.Servicio;

import cuadrantes.Turno;

import empleados.Permuta;

public class JDBCPermutaDAO implements IPermutaDAO{
	
private Connection conn;
	
	public JDBCPermutaDAO(){
		
		conn = ConnectionManager.getInstance().checkOut();
		
		
	}

	@Override
	public void delete(Integer id) {
		String sql = "DELETE FROM permutas WHERE (idpermuta = ?) ";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, id);
            stmt.executeUpdate();
            
        } catch (SQLException e) {
            System.err.println("Message: " + e.getMessage());
            System.err.println("SQLState: " + e.getSQLState());
            System.err.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }
        }
		
	}

	@Override
	public void insert(Permuta p) {
		String sql = "INSERT INTO permutas (dni1, dni2, servicio, dnicliente, dia, horaini1, horafin1, horaini2, horafin2, estado, aprobacion) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);           
            stmt.setString(1, p.getDNI1());
            stmt.setString(2, p.getDNI2());
            stmt.setString(3, p.getServicio().getNombre());
            stmt.setString(4, p.getServicio().getCIF());
            stmt.setDate(5, p.getDia());
            stmt.setTime(6, p.getTurno1().getHoraini());
            stmt.setTime(7, p.getTurno1().getHorafin());
            stmt.setTime(8, p.getTurno2().getHoraini());
            stmt.setTime(9, p.getTurno2().getHorafin());
            stmt.setString(10, p.getEstado());
            stmt.setString(11, p.getAceptacion());
            stmt.executeUpdate();

        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
		
	}

	@Override
	public void modificapermutaestado(int idpermuta, String estado) {
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		ResultSet result = null;
		String sql = "UPDATE permutas SET estado = ?" +"WHERE (idpermuta = ?)";
				
		
		try{
			stmt = conn.prepareStatement(sql);
			
			stmt.setString(1, estado);
            stmt.setInt(2, idpermuta);
		
			stmt.executeUpdate();
		}catch(SQLException e){
			System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
		
	}

	@Override
	public Permuta select(Integer id) {
		PreparedStatement stmt = null;
        ResultSet result = null;
    	Permuta p = new Permuta();
    	Turno t1 = new Turno();
    	Turno t2 = new Turno();
    	Servicio s = new Servicio();      
        String sql = "SELECT * FROM permutas WHERE (idpermuta = ?)";
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, id);
            result = stmt.executeQuery();

            if(result.next()){
            	p.setDNI1(result.getString("dni1"));
            	p.setDNI2(result.getString("dni2"));
            	s.setNombre(result.getString("servicio"));
            	s.setCIF(result.getString("dnicliente"));
            	p.setDia(result.getDate("dia"));
            	t1.setHoraini(result.getTime("horaini1"));
            	t1.setHorafin(result.getTime("horafin1"));
            	t2.setHoraini(result.getTime("horaini2"));
            	t2.setHorafin(result.getTime("horafin2"));
            	p.setEstado(result.getString("estado"));
            	p.setAceptacion(result.getString("aprobacion"));
            	p.setServicio(s);     
            	p.setTurno1(t1);
            	p.setTurno2(t2);         
            }
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
        return p;
	}

	@Override
	public List<Permuta> selectpermutasporestado(String estado) {
		PreparedStatement stmt = null;
        ResultSet result = null;        
        List<Permuta> listapermutas = new LinkedList<Permuta>();
        Permuta p = new Permuta();
        Turno t1 = new Turno();
    	Turno t2 = new Turno();
    	Servicio s = new Servicio();
        String sql = "SELECT * FROM permutas WHERE (estado = ?) ORDER BY aprobacion";
    
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, estado);            
            result = stmt.executeQuery();

            while (result.next()) {
            	p=new Permuta();           	
            	p.setIdPermuta(result.getInt("idpermuta"));
            	p.setDNI1(result.getString("dni1"));
            	p.setDNI2(result.getString("dni2"));
            	s.setNombre(result.getString("servicio"));
            	s.setCIF(result.getString("dnicliente"));
            	p.setDia(result.getDate("dia"));
            	t1.setHoraini(result.getTime("horaini1"));
            	t1.setHorafin(result.getTime("horafin1"));
            	t2.setHoraini(result.getTime("horaini2"));
            	t2.setHorafin(result.getTime("horafin2"));
            	p.setEstado(result.getString("estado"));
            	p.setAceptacion(result.getString("aprobacion"));
            	p.setServicio(s);     
            	p.setTurno1(t1);
            	p.setTurno2(t2);
                listapermutas.add(p);
            }
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
        return listapermutas;
	}

	@Override
	public List<Permuta> selectpermutassolicitadasaempleado(String dni) {
		PreparedStatement stmt = null;
        ResultSet result = null;        
        List<Permuta> listapermutas = new LinkedList<Permuta>();
        Permuta p = new Permuta();
        Turno t1 = new Turno();
    	Turno t2 = new Turno();
    	Servicio s = new Servicio();
        String sql = "SELECT * FROM permutas WHERE (dni2 = ? ) ORDER BY aprobacion, estado";   
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, dni);           
            result = stmt.executeQuery();

            while (result.next()) {
            	p=new Permuta();
            	p.setIdPermuta(result.getInt("idpermuta"));
            	p.setDNI1(result.getString("dni1"));
            	p.setDNI2(result.getString("dni2"));
            	s.setNombre(result.getString("servicio"));
            	s.setCIF(result.getString("dnicliente"));
            	p.setDia(result.getDate("dia"));
            	t1.setHoraini(result.getTime("horaini1"));
            	t1.setHorafin(result.getTime("horafin1"));
            	t2.setHoraini(result.getTime("horaini2"));
            	t2.setHorafin(result.getTime("horafin2"));
            	p.setEstado(result.getString("estado"));
            	p.setAceptacion(result.getString("aprobacion"));
            	p.setServicio(s);     
            	p.setTurno1(t1);
            	p.setTurno2(t2);
                listapermutas.add(p);
            }
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
        return listapermutas;
	}

	@Override
	public List<Permuta> selectpermutassolicitadasaempleado(String dni,
			String aprobacion) {
		PreparedStatement stmt = null;
        ResultSet result = null;       
        List<Permuta> listapermutas = new LinkedList<Permuta>();
        Permuta p = new Permuta();
        Turno t1 = new Turno();
    	Turno t2 = new Turno();
    	Servicio s = new Servicio();
        String sql = "SELECT * FROM permutas WHERE (dni2 = ? AND aprobacion =? ) ORDER BY estado";
    
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, dni);
            stmt.setString(2, aprobacion);
            result = stmt.executeQuery();

            while (result.next()) {
            	p=new Permuta();
            	p.setIdPermuta(result.getInt("idpermuta"));
            	p.setDNI1(result.getString("dni1"));
            	p.setDNI2(result.getString("dni2"));
            	s.setNombre(result.getString("servicio"));
            	s.setCIF(result.getString("dnicliente"));
            	p.setDia(result.getDate("dia"));
            	t1.setHoraini(result.getTime("horaini1"));
            	t1.setHorafin(result.getTime("horafin1"));
            	t2.setHoraini(result.getTime("horaini2"));
            	t2.setHorafin(result.getTime("horafin2"));
            	p.setEstado(result.getString("estado"));
            	p.setAceptacion(result.getString("aprobacion"));
            	p.setServicio(s);     
            	p.setTurno1(t1);
            	p.setTurno2(t2);
                listapermutas.add(p);
            }
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
        return listapermutas;
	}

	@Override
	public List<Permuta> selectpermutassolicitadasporempleado(String dni) {
		PreparedStatement stmt = null;
        ResultSet result = null;        
        List<Permuta> listapermutas = new LinkedList<Permuta>();
        Permuta p = new Permuta();
        Turno t1 = new Turno();
    	Turno t2 = new Turno();
    	Servicio s = new Servicio();
    	String sql = "SELECT * FROM permutas WHERE (dni1 = ?) ORDER BY aprobacion, estado";    
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, dni);           
            result = stmt.executeQuery();

            while (result.next()) {
            	p=new Permuta();
            	p.setIdPermuta(result.getInt("idpermuta"));
            	p.setDNI1(result.getString("dni1"));
            	p.setDNI2(result.getString("dni2"));
            	s.setNombre(result.getString("servicio"));
            	s.setCIF(result.getString("dnicliente"));
            	p.setDia(result.getDate("dia"));
            	t1.setHoraini(result.getTime("horaini1"));
            	t1.setHorafin(result.getTime("horafin1"));
            	t2.setHoraini(result.getTime("horaini2"));
            	t2.setHorafin(result.getTime("horafin2"));
            	p.setEstado(result.getString("estado"));
            	p.setAceptacion(result.getString("aprobacion"));
            	p.setServicio(s);     
            	p.setTurno1(t1);
            	p.setTurno2(t2);
                listapermutas.add(p);
            }
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
        return listapermutas;
	}
	
	public void finalize(){
		ConnectionManager.getInstance().checkIn(conn);
		
	}

	@Override
	public void modificapermutaaprobacion(int idpermuta, String aprobacion) {
		PreparedStatement stmt = null;
		ResultSet result = null;
		String sql = "UPDATE permutas SET aprobacion = ?" +"WHERE (idpermuta = ?)";		
		try{
			stmt = conn.prepareStatement(sql);			
			stmt.setString(1, aprobacion);
            stmt.setInt(2, idpermuta);		
			stmt.executeUpdate();
		}catch(SQLException e){
			System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
		
	}

}
